package com.nsoft.html5npub.data;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.nsoft.html5npub.objects.BookItem;
import com.nsoft.html5npub.objects.BookmarkItem;
import com.nsoft.html5npub.utils.Constant;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {
	
	public final int DB_UPDATE_FAILED=-1;
	public final int DB_UPDATE_NONE=0;
	
	public DatabaseHandler(Context context) {
		super(context,Constant.db.DB_NAME,
				null,Constant.db.DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		String bookTable="CREATE TABLE "+
			Constant.db.DB_TABLE_BOOKS+"("+
			Constant.xml.XML_TAG_CONTENTID+
				" VARCHAR(50) PRIMARY KEY,"+
			Constant.xml.XML_TAG_CONTENTNAME+" VARCHAR,"+
			Constant.xml.XML_TAG_FILENAME+" VARCHAR,"+
			Constant.xml.XML_TAG_IMGPATH+" VARCHAR,"+
			Constant.xml.XML_TAG_DOWNLOADPATH+" VARCHAR,"+
			Constant.xml.XML_TAG_VERSION+" VARCHAR,"+
			Constant.xml.XML_TAG_STATE+" VARCHAR,"+
			Constant.xml.XML_TAG_REDOWNLOAD+" VARCHAR,"+
			Constant.xml.XML_TAG_DOWNLOADED+" VARCHAR)";
		
		String bookmarkTable="CREATE TABLE "+
			Constant.db.DB_TABLE_BOOKMARK+"("+
			Constant.xml.XML_TAG_CONTENTID+" VARCHAR(100),"+
			Constant.xml.XML_CONFIG_TAG_PAGEID+" VARCHAR(100),"+
			Constant.db.DB_BOOKMARK_PAGEINDEX_X+" INT,"+
			Constant.db.DB_BOOKMARK_PAGEINDEX_Y+" INT,"+
			"PRIMARY KEY ("+
				Constant.xml.XML_TAG_CONTENTID+","+
				Constant.xml.XML_CONFIG_TAG_PAGEID+"))";
		
		db.execSQL(bookTable);
		db.execSQL(bookmarkTable);	
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, 
			int oldVersion,int newVersion) {
		String dropBookTable="DROP TABLE IF EXISTS "+
					Constant.db.DB_TABLE_BOOKS;
		String dropBookmarkTable="DROP TABLE IF EXISTS "+
					Constant.db.DB_TABLE_BOOKMARK;
		
		db.execSQL(dropBookTable);
		db.execSQL(dropBookmarkTable);
		
		onCreate(db);
	}

	public boolean addBook(BookItem book){
		SQLiteDatabase db=this.getWritableDatabase();
		ContentValues values=new ContentValues();
	    values.put(Constant.xml.XML_TAG_CONTENTID,
	    			book.getContentId());
	    values.put(Constant.xml.XML_TAG_CONTENTNAME,
	    			book.getContentName()); 
	    values.put(Constant.xml.XML_TAG_FILENAME,
    				book.getFilename());
	    values.put(Constant.xml.XML_TAG_IMGPATH,
    			book.getImgPath());
	    values.put(Constant.xml.XML_TAG_DOWNLOADPATH,
    			book.getDownloadPath());
	    values.put(Constant.xml.XML_TAG_VERSION,
    			book.getVersion());
	    values.put(Constant.xml.XML_TAG_STATE,
    			book.getState());
	    values.put(Constant.xml.XML_TAG_REDOWNLOAD,
    			book.getReDownload());
	    values.put(Constant.xml.XML_TAG_DOWNLOADED,
    			book.getDownloaded());
	    long rowId=db.insert(Constant.db.
	    			DB_TABLE_BOOKS,null,values);
	    db.close(); 
		
	    return rowId!=DB_UPDATE_FAILED;
	}
	
	public BookItem getBookById(String bookId){
		BookItem book=new BookItem();
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor cursor=db.query(
				Constant.db.DB_TABLE_BOOKS, 
				new String[]{
					Constant.xml.XML_TAG_CONTENTID,
					Constant.xml.XML_TAG_CONTENTNAME,
					Constant.xml.XML_TAG_FILENAME,
					Constant.xml.XML_TAG_IMGPATH,
					Constant.xml.XML_TAG_DOWNLOADPATH,
					Constant.xml.XML_TAG_VERSION,
					Constant.xml.XML_TAG_STATE,
					Constant.xml.XML_TAG_REDOWNLOAD,
					Constant.xml.XML_TAG_DOWNLOADED }, 
	            Constant.xml.XML_TAG_CONTENTID+"=?",
	            new String[]{ bookId },
	            null,null,null,null);
		
	    if (cursor!=null)
	        cursor.moveToFirst();
	    book.setContentId(bookId);
	    book.setContentName(cursor.getString(1));
	    book.setFilename(cursor.getString(2));
	    book.setImgPath(cursor.getString(3));
	    book.setDownloadPath(cursor.getString(4));
	    book.setVersion(cursor.getString(5));
	    book.setState(cursor.getString(6));
	    book.setReDownload(cursor.getString(7));
	    book.setDownloaded(cursor.getString(8));
	    
	    cursor.close();
	    db.close();
	    
	    return book;
	}
	
	public boolean isBookExist(String bookId){
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor cur=db.query(
				Constant.db.DB_TABLE_BOOKS,
				new String[]{
					Constant.xml.XML_TAG_CONTENTID },
				Constant.xml.XML_TAG_CONTENTID+"=?",
				new String[]{ bookId },
				null,null,null,null);
		
		boolean retVal=cur!=null;
		cur.close();
		db.close();
		
		return retVal;
	}
	
	public Map<String,BookItem> getBookMap(){
		Map<String,BookItem> bookList=
				new HashMap<String,BookItem>();
		
		String selectQuery="SELECT * FROM "+
    			Constant.db.DB_TABLE_BOOKS;
 
	    SQLiteDatabase db=this.getWritableDatabase();
	    Cursor cursor=db.rawQuery(selectQuery,null);
	 
	    BookItem book=null;
	    if (cursor.moveToFirst()){
	        do{
	            book=new BookItem();
	            book.setContentId(cursor.getString(0));
	            book.setContentName(cursor.getString(1));
	            book.setFilename(cursor.getString(2));
	            book.setImgPath(cursor.getString(3));
	            book.setDownloadPath(cursor.getString(4));
	            book.setVersion(cursor.getString(5));
	            book.setState(cursor.getString(6));
	            book.setReDownload(cursor.getString(7));
	            book.setDownloaded(cursor.getString(8));
	            
	            bookList.put(book.getContentId(),book);
	        }while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
	    
		return bookList;
	}
	
	public BookItem[] getBookList(){
		List<BookItem> bookList=new ArrayList<BookItem>();
		
	    String selectQuery="SELECT * FROM "+
	    			Constant.db.DB_TABLE_BOOKS;
	 
	    SQLiteDatabase db=this.getWritableDatabase();
	    Cursor cursor=db.rawQuery(selectQuery,null);
	 
	    BookItem book=null;
	    if (cursor.moveToFirst()){
	        do{
	            book=new BookItem();
	            book.setContentId(cursor.getString(0));
	            book.setContentName(cursor.getString(1));
	            book.setFilename(cursor.getString(2));
	            book.setImgPath(cursor.getString(3));
	            book.setDownloadPath(cursor.getString(4));
	            book.setVersion(cursor.getString(5));
	            book.setState(cursor.getString(6));
	            book.setReDownload(cursor.getString(7));
	            book.setDownloaded(cursor.getString(8));
	            
	            bookList.add(book);
	        }while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
	    
	    return bookList.toArray(new BookItem[]{});
	}
	
	public boolean updateBook(BookItem book){
		SQLiteDatabase db=this.getWritableDatabase();

		ContentValues values=new ContentValues();
	    values.put(Constant.xml.XML_TAG_CONTENTNAME,
	    			book.getContentName()); 
	    values.put(Constant.xml.XML_TAG_FILENAME,
					book.getFilename());
	    values.put(Constant.xml.XML_TAG_IMGPATH,
					book.getImgPath());
	    values.put(Constant.xml.XML_TAG_DOWNLOADPATH,
					book.getDownloadPath());
	    values.put(Constant.xml.XML_TAG_VERSION,
					book.getVersion());
	    values.put(Constant.xml.XML_TAG_STATE,
					book.getState());
	    values.put(Constant.xml.XML_TAG_REDOWNLOAD,
					book.getReDownload());
	    values.put(Constant.xml.XML_TAG_DOWNLOADED,
					book.getDownloaded());
	    
	    int retVal=db.update(Constant.db.DB_TABLE_BOOKS, 
    		values,Constant.xml.XML_TAG_CONTENTID+"=?",
	            new String[] { book.getContentId() });
	    db.close();
	    
	    return retVal!=DB_UPDATE_NONE;
	}
	
	public boolean deleteBook(String bookId){
		SQLiteDatabase db=this.getWritableDatabase();
		int retVal=db.delete(Constant.db.DB_TABLE_BOOKS,
				Constant.xml.XML_TAG_CONTENTID+"=?",
				new String[] { bookId });
		db.close();
		
		return retVal!=DB_UPDATE_NONE;
	}
	
	public boolean addBookmark(BookmarkItem bmItem){
		SQLiteDatabase db=this.getWritableDatabase();
		ContentValues values=new ContentValues();
	    values.put(Constant.xml.XML_TAG_CONTENTID,
	    			bmItem.getContentId());
	    values.put(Constant.xml.XML_CONFIG_TAG_PAGEID,
	    			bmItem.getPageId());
	    values.put(Constant.db.DB_BOOKMARK_PAGEINDEX_X,
    			Integer.toString(bmItem.getPageIndexX())); 
	    values.put(Constant.db.DB_BOOKMARK_PAGEINDEX_Y,
				Integer.toString(bmItem.getPageIndexY()));
	    long rowId=db.insert(Constant.db.
	    			DB_TABLE_BOOKMARK,null,values);
	    db.close(); 
		
		return rowId!=DB_UPDATE_FAILED;
	}
	
	public Map<String,BookmarkItem> getBookMarkMap(String bookId){
		Map<String,BookmarkItem> bookmarkList=
				new HashMap<String,BookmarkItem>();
	
		String selectQuery="SELECT * FROM "+
				Constant.db.DB_TABLE_BOOKMARK+" "+
				"WHERE "+Constant.xml.XML_TAG_CONTENTID+
				"=?";
	
	    SQLiteDatabase db=this.getWritableDatabase();
	    Cursor cursor=db.rawQuery(selectQuery,
	    				new String[]{ bookId });
	 
	    BookmarkItem bm=null;
	    if (cursor.moveToFirst()){
	        do{
	            bm=new BookmarkItem();
	            bm.setContentId(cursor.getString(0));
	            bm.setPageId(cursor.getString(1));
	            bm.setPageIndexX(cursor.getInt(2));
	            bm.setPageIndexY(cursor.getInt(3));
	            
	            bookmarkList.put(bm.getPageId(),bm);
	        }while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
		
		return bookmarkList;
	}
	
	/**
	public Map<BookCoord,BookmarkItem> getBookMarkMap(String bookId){
		Map<BookCoord,BookmarkItem> bookmarkList=
					new HashMap<BookCoord,BookmarkItem>();
		
		String selectQuery="SELECT * FROM "+
				Constant.db.DB_TABLE_BOOKMARK+" "+
				"WHERE "+Constant.xml.XML_TAG_CONTENTID+
				"=?";
 
	    SQLiteDatabase db=this.getWritableDatabase();
	    Cursor cursor=db.rawQuery(selectQuery,
	    				new String[]{ bookId });
	 
	    BookmarkItem bm=null;
	    if (cursor.moveToFirst()){
	        do{
	            bm=new BookmarkItem();
	            bm.setContentId(cursor.getString(0));
	            bm.setPageIndexX(cursor.getInt(1));
	            bm.setPageIndexY(cursor.getInt(2));
	            
	            bookmarkList.put(new BookCoord(
	            			bm.getPageIndexX(),
	            			bm.getPageIndexY()),bm);
	        }while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
		
		return bookmarkList;
	}
	*/
	
	/**
	public List<BookmarkItem> getBookMarkList(String bookId){
		List<BookmarkItem> bookmarkList=
						new ArrayList<BookmarkItem>();
		
		String selectQuery="SELECT * FROM "+
				Constant.db.DB_TABLE_BOOKMARK+" "+
				"WHERE "+Constant.xml.XML_TAG_CONTENTID+
				"=?";
 
	    SQLiteDatabase db=this.getWritableDatabase();
	    Cursor cursor=db.rawQuery(selectQuery,
	    				new String[]{ bookId });
	 
	    BookmarkItem bm=null;
	    if (cursor.moveToFirst()){
	        do{
	            bm=new BookmarkItem();
	            bm.setContentId(cursor.getString(0));
	            bm.setPageId(cursor.getString(1));
	            bm.setPageIndexX(cursor.getInt(2));
	            bm.setPageIndexY(cursor.getInt(3));
	            
	            bookmarkList.add(bm);
	        }while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
		
		return bookmarkList;
	}
	*/
	
	public boolean isBookmarkExist(BookmarkItem bmItem){
		SQLiteDatabase db=this.getReadableDatabase();
		String criteria=Constant.xml.XML_TAG_CONTENTID+"=? AND "+
					Constant.xml.XML_CONFIG_TAG_PAGEID+"=?";
		Cursor cur=db.query(
				Constant.db.DB_TABLE_BOOKMARK,
				new String[]{
					Constant.xml.XML_TAG_CONTENTID,
					Constant.xml.XML_CONFIG_TAG_PAGEID },
				criteria,
				new String[]{ bmItem.getContentId(),
						bmItem.getPageId() },
				null,null,null,null);
		
		boolean retVal=cur!=null;
		cur.close();
		db.close();
		
		return retVal;
	}
	
	
	public boolean isBookmarkExist(String contentId,
					int pageIndexX,int pageIndexY){
		
		SQLiteDatabase db=this.getReadableDatabase();
		String criteria=Constant.xml.XML_TAG_CONTENTID+"=? AND "+
				Constant.db.DB_BOOKMARK_PAGEINDEX_X+"=? AND "+
				Constant.db.DB_BOOKMARK_PAGEINDEX_Y+"=?";
		Cursor cur=db.query(
				Constant.db.DB_TABLE_BOOKMARK,
				new String[]{
					Constant.xml.XML_TAG_CONTENTID },
				criteria,
				new String[]{ contentId,
						Integer.toString(pageIndexX),
						Integer.toString(pageIndexY) },
				null,null,null,null);
		
		boolean retVal=cur!=null;
		cur.close();
		db.close();
		
		return retVal;
	}
	
	
	public boolean deleteBookmark(BookmarkItem bmItem){
		SQLiteDatabase db=this.getWritableDatabase();
		String criteria=Constant.xml.XML_TAG_CONTENTID+"=? AND "+
				Constant.xml.XML_CONFIG_TAG_PAGEID+"=?";
		int retVal=db.delete(Constant.db.DB_TABLE_BOOKMARK,
				criteria,new String[] { bmItem.getContentId(),
									bmItem.getPageId() });
		db.close();
		
		return retVal!=DB_UPDATE_NONE;
	}
	
	/**
	 * delete all the bookmark related to a specified book
	 * 
	 * @param bookId
	 * @return
	 */
	public boolean deleteBookmarkOfBook(String bookId){
		SQLiteDatabase db=this.getWritableDatabase();
		String criteria=Constant.xml.XML_TAG_CONTENTID+"=?";
		int retVal=db.delete(Constant.db.DB_TABLE_BOOKMARK,
				criteria,new String[] { bookId });
		db.close();
		
		return retVal!=DB_UPDATE_NONE;
	}
}
